const { db, sqlExec } = require('../db/mysql57');

const userLibrary = {
    //
    async loginByOpenid(openid) {
        let sql = `select
                u.id as id,
                u.username as username,
                UserType.name as userType,
                u.allowLogin as allowLogin
            from
                User as u
            left join
                UserType
            on UserType.id = u.userTypeId
            where openid = ? Limit 1`;
        let data = [openid];
        return sqlExec(sql, data);
    },
    /*
     * 新增用户
     * userInfo = {"openid": "string"}
     */
    async addUser(openid) {
        let sql1 = `insert into User(
                openid, username, userTypeId
            ) values(?, ?, (
                select id from UserType where name = ? LIMIT 1
            ))`;
        let sql2 = `select
                u.id as id,
                u.username as username,
                ut.name as userType,
                u.allowLogin as allowLogin
            from
                User as u
            left join
                UserType as ut
            on ut.id = u.userTypeId
            where u.id = ? Limit 1`;
        let data1 = [ openid, 'wx_' + openid.slice(0, 3), 'user' ];
        try {
            let result = await sqlExec(sql1, data1);
            let new_pk = result.insertId;
            let data2 = [ new_pk ];
            return sqlExec(sql2, data2);
        } catch (err) {
            throw err;
        }
    },

    /*
     * 删除用户
     * userInfo = {"openid": "string"}
     */
    async deleteUserByOpenid(userInfo) {
        if (!userInfo || !userInfo.openid) {
            return {success: 0, msg: "openid不能为空"};
        }
    
        let sql = "DELETE FROM Users WHERE openid = ?";
        let data = [userInfo.openid];
    
        let result = await new Promise((resolve, reject) => {
            db.query(sql, data, (err, res) => {
                if (err) {
                    reject({success: 0, err: err, msg: "删除数据库记录时异常"});
                } else {
                    resolve({success: 1, msg: "删除成功"});
                }
            });
        });
    
        return result;
    },
    
    /*
     * 更新用户名
     * userInfo = {"uid": "string", "username": "string"}
     */
    async updateUserNameByUid(userInfo) {
        if (!userInfo || !userInfo.uid || !userInfo.username) {
            return {success: 0, msg: "userInfo对象不完整"};
        }
        let sql = `UPDATE Users SET ? WHERE uid = ?`;
        let values = [userInfo.username, userInfo.uid];
    
        let result = await new Promise((resolve, reject) => {
            db.query(sql, values, (err, res) => {
                if (err) {
                    reject({success: 0, err: err, msg: "更新数据库记录时异常"});
                } else {
                    resolve({success: 1, res: res, msg: "更新成功"});
                }
            });
        });
    
        return result;
    },
    
    /*
     * 获取用户信息
     * userInfo = {"uid": "string"}
     */
    async getUserInfoByUid(uid) {
        let sql = `SELECT
                id, userIconId, username, phone, bio
            FROM User
            WHERE id = ?
            LIMIT 1`;
        let data = [ uid ];
        return sqlExec(sql, data);
    },
    
    // /*
    //  * 用户修改密码
    //  * userInfo = {"uid": "string", "password": "string"}
    //  */
    // async updateUserPassword(userInfo) {
    //     if (!userInfo || !userInfo.uid || !userInfo.password) {
    //         return {success: 0, msg: "userInfo对象不完整"};
    //     }
    
    //     // 准备SQL和数据，这里假设密码字段名为`password`
    //     let sql = "UPDATE Users SET password = ? WHERE uid = ?";
    //     let data = [userInfo.password, userInfo.uid];
    
    //     let result = await new Promise((resolve, reject) => {
    //         db.query(sql, data, (err, res) => {
    //             if (err) {
    //                 reject({success: 0, err: err, msg: "更新密码时数据库操作异常"});
    //             } else {
    //                 resolve({success: 1, msg: "密码更新成功"});
    //             }
    //         });
    //     });
    
    //     return result;
    // },
    
    // /*
    //  * 查询用户是否存在
    //  * userInfo = {"openid": "string"}
    //  */
    // async checkUserExistByOpenid(userInfo) {
    //     if (!userInfo || !userInfo.openid) {
    //         return {result: false, success: 0, msg: "userInfo对象不完整"};
    //     }
    //     let sql = `SELECT * FROM Users WHERE openid = ? LIMIT 1`;
    //     let data = [userInfo.openid];
    
    //     let result = await new Promise((resolve, reject) => {
    //         db.query(sql, data, (err, res) => {
    //             if (err) {
    //                 reject({success: 0, err: err, msg: "查询数据库记录时异常"});
    //             } else if (res.length > 0) {
    //                 resolve({success: 1, userInfo: res[0], msg: "查询成功"});
    //             } else {
    //                 resolve({success: 0, msg: "未找到该用户"});
    //             }
    //         });
    //     });
    
    //     if (result.success === 1) {
    //         return {result: true, msg: "用户已存在", uid: result.userInfo.uid};
    //     }
    //     // 用户不存在 或者 查询数据库时出错
    //     return {result: false, msg: result.msg};
    // },
    // /*
    //  * 使用手机号和密码查询用户信息
    //  */
    // async selectUserByPhoneAndPassWord(loginData) {
    //     let sql = `SELECT uid, openid FROM Users WHERE phone = ? AND password = ? LIMIT 1`;
    //     let data = [loginData.phone, loginData.password];
    //     let result = await new Promise((resolve, reject) => {
    //         db.query(sql, data, (err, res) => {
    //             if (err) {
    //                 reject({success: 0, err: err, msg: "查询数据库记录时异常"});
    //             } else if (res.length > 0) {
    //                 resolve({success: 1, userInfo: res[0], msg: "查询成功"});
    //             } else {
    //                 resolve({success: 0, msg: "未找到该用户"});
    //             }
    //         });
    //     });
    //     return result;
    // },

    /*
     * 修改用户个人信息
     */
    async setUserInfo(uid, userIconId, username, phone, bio) {
        let sql = `UPDATE User SET userIconId = ?, username = ?, phone = ?, bio = ? WHERE id = ?`;
        let data = [ userIconId, username, phone, bio, uid ];
        return sqlExec(sql, data);
    },
    // 账号密码登录
    async adminLogin(username, password) {
        let sql = `SELECT
                u.id as uid,
                u.username as username,
                ut.name as userType
            FROM User as u
            LEFT JOIN UserType as ut on u.userTypeId = ut.id
            WHERE u.username = ? AND u.password = ? AND ut.name != 'User'`;
        let data = [ username, password ];
        return sqlExec(sql, data);
    },
    async getAllUser(page) {
        let sql = `select
                u.id as uid,
                u.openid as openid,
                u.username as username,
                u.phone as phone,
                u.bio as bio,
                ut.name as userType,
                u.allowLogin as allowLogin,
                u.termsAgreed as termsAgreed
            from User as u
            left join UserType as ut on u.userTypeId = ut.id
            where ut.name != 'Admin'
            order by u.id desc
            limit ?, 50`;
        let data = [ (page-1) * 50 ];
        return sqlExec(sql, data);
    }
};

module.exports = userLibrary;
